<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/xhtml;charset=UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=9"/>
<meta name="generator" content="Doxygen 1.8.20"/>
<meta name="viewport" content="width=device-width, initial-scale=1"/>
<title>libxlsxwriter: Working with Memory and Performance</title>
<link href="tabs.css" rel="stylesheet" type="text/css"/>
<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript" src="dynsections.js"></script>
<link href="search/search.css" rel="stylesheet" type="text/css"/>
<script type="text/javascript" src="search/searchdata.js"></script>
<script type="text/javascript" src="search/search.js"></script>
<link href="doxygen.css" rel="stylesheet" type="text/css" />
<link href="customdoxygen.css" rel="stylesheet" type="text/css"/>
</head>
<body>
<div id="top"><!-- do not remove this div, it is closed by doxygen! -->
<div id="titlearea">
<table cellspacing="0" cellpadding="0">
 <tbody>
 <tr style="height: 56px;">
  <td id="projectalign" style="padding-left: 0.5em;">
   <div id="projectname">libxlsxwriter
   </div>
  </td>
 </tr>
 </tbody>
</table>
</div>
<!-- end header part -->
<!-- Generated by Doxygen 1.8.20 -->
<script type="text/javascript">
/* @license magnet:?xt=urn:btih:cf05388f2679ee054f2beb29a391d25f4e673ac3&amp;dn=gpl-2.0.txt GPL-v2 */
var searchBox = new SearchBox("searchBox", "search",false,'Search');
/* @license-end */
</script>
<script type="text/javascript" src="menudata.js"></script>
<script type="text/javascript" src="menu.js"></script>
<script type="text/javascript">
/* @license magnet:?xt=urn:btih:cf05388f2679ee054f2beb29a391d25f4e673ac3&amp;dn=gpl-2.0.txt GPL-v2 */
$(function() {
  initMenu('',true,false,'search.php','Search');
  $(document).ready(function() { init_search(); });
});
/* @license-end */</script>
<div id="main-nav"></div>
<!-- window showing the filter options -->
<div id="MSearchSelectWindow"
     onmouseover="return searchBox.OnSearchSelectShow()"
     onmouseout="return searchBox.OnSearchSelectHide()"
     onkeydown="return searchBox.OnSearchSelectKey(event)">
</div>

<!-- iframe showing the search results (closed by default) -->
<div id="MSearchResultsWindow">
<iframe src="javascript:void(0)" frameborder="0" 
        name="MSearchResults" id="MSearchResults">
</iframe>
</div>

</div><!-- top -->
<div class="PageDoc"><div class="header">
  <div class="headertitle">
<div class="title">Working with Memory and Performance </div>  </div>
</div><!--header-->
<div class="contents">
<div class="toc"><h3>Table of Contents</h3>
<ul><li class="level1"><a href="#ww_mem_constant">Constant Memory Mode</a></li>
<li class="level1"><a href="#ww_mem_row_order">Row Column Order</a></li>
<li class="level1"><a href="#ww_mem_inline_strings">Inline strings</a></li>
<li class="level1"><a href="#ww_mem_performance">Performance</a></li>
</ul>
</div>
<div class="textblock"><h1><a class="anchor" id="ww_mem_constant"></a>
Constant Memory Mode</h1>
<p>By default libxlsxwriter holds all cell data in memory. This is to allow non-sequential data storage and also to allow future features where formatting is applied separately from the data.</p>
<p>The effect of this is that for large files libxlsxwriter can consume a lot of memory.</p>
<p>Fortunately, this memory usage can be reduced almost completely by using <a class="el" href="workbook_8h.html#a8ca9bd8c30c618b81ca6180f78b03323" title="Create a new workbook object, and set the workbook options.">workbook_new_opt()</a> and the <a class="el" href="structlxw__workbook__options.html" title="Workbook options.">lxw_workbook_options</a> <code>constant_memory</code> property:</p>
 <div class="fragment"><div class="line"><span class="preprocessor">#include &quot;xlsxwriter.h&quot;</span></div>
<div class="line"> </div>
<div class="line"><span class="keywordtype">int</span> main() {</div>
<div class="line"> </div>
<div class="line">    <a class="code" href="common_8h.html#aaf33b4e2179dcc466359277210774ce3">lxw_row_t</a> row;</div>
<div class="line">    <a class="code" href="common_8h.html#a4fedd8dede664b070e84e850a3f92fd2">lxw_col_t</a> col;</div>
<div class="line">    <a class="code" href="common_8h.html#aaf33b4e2179dcc466359277210774ce3">lxw_row_t</a> max_row = 1000;</div>
<div class="line">    <a class="code" href="common_8h.html#a4fedd8dede664b070e84e850a3f92fd2">lxw_col_t</a> max_col = 50;</div>
<div class="line"> </div>
<div class="line">    <span class="comment">/* Set the worksheet options. */</span></div>
<div class="line">    <a class="code" href="structlxw__workbook__options.html">lxw_workbook_options</a> options = {.constant_memory = <a class="code" href="common_8h.html#a57bffaf0ff3cb4e9f4f7f2b0b6dad349a7850bcc34c18efe2e45372f4069be0bf">LXW_TRUE</a>,</div>
<div class="line">                                    .tmpdir = NULL,</div>
<div class="line">                                    .use_zip64 = <a class="code" href="common_8h.html#a57bffaf0ff3cb4e9f4f7f2b0b6dad349a847f574cc183a1d8a4e3800fe28091f4">LXW_FALSE</a>};</div>
<div class="line"> </div>
<div class="line">    <span class="comment">/* Create a new workbook with options. */</span></div>
<div class="line">    <a class="code" href="structlxw__workbook.html">lxw_workbook</a>  *workbook  = <a class="code" href="workbook_8h.html#a8ca9bd8c30c618b81ca6180f78b03323">workbook_new_opt</a>(<span class="stringliteral">&quot;constant_memory.xlsx&quot;</span>, &amp;options);</div>
<div class="line">    <a class="code" href="structlxw__worksheet.html">lxw_worksheet</a> *worksheet = <a class="code" href="workbook_8h.html#a81d456b4f65a464e78e4a0030ecc3c2e">workbook_add_worksheet</a>(workbook, NULL);</div>
<div class="line"> </div>
<div class="line">    <span class="keywordflow">for</span> (row = 0; row &lt; max_row; row++) {</div>
<div class="line">        <span class="keywordflow">for</span> (col = 0; col &lt; max_col; col++) {</div>
<div class="line">            <a class="code" href="worksheet_8h.html#ad9fc47d3beaa2ab4759414e8580c2289">worksheet_write_number</a>(worksheet, row, col, 123.45, NULL);</div>
<div class="line">        }</div>
<div class="line">    }</div>
<div class="line"> </div>
<div class="line">    <span class="keywordflow">return</span> <a class="code" href="workbook_8h.html#ad9e7aeebc0fd43562db5bcd527b2ee5e">workbook_close</a>(workbook);</div>
<div class="line">}</div>
</div><!-- fragment --><p>This optimization works by flushing each row after a subsequent row is written. In this way the largest amount of data held in memory for a worksheet is the amount of data required to hold a single row of data.</p>
<p>If required, this memory usage can be reduced even more by setting <code>LXW_COL_MAX</code> in worksheet.c from 16384 down to a value that matches the maximum column that is likely to be encountered.</p>
<p>The trade-off when using <code>constant_memory</code> mode is that data must be added sequentially in row order and you won't be able to take advantage of any functions that manipulate cell data after it is written. One such feature is <a class="el" href="worksheet_8h.html#ab9b7fb95e1bd9b0da70befd0d37a9173" title="Set the properties for a row of cells.">worksheet_set_row()</a> which in <code>constant_memory</code> mode can only be used when writing data to the current row. Another is <a class="el" href="worksheet_8h.html#ad5a2a09ec65c0f286b756235c7327225" title="Merge a range of cells.">worksheet_merge_range()</a> which can only be applied to a merged range in the current row.</p>
<h1><a class="anchor" id="ww_mem_row_order"></a>
Row Column Order</h1>
<p>Since each new row flushes the previous row, data must be written in sequential row order when <code>constant_memory</code> mode is on:</p>
<div class="fragment"><div class="line"><a class="code" href="structlxw__workbook.html">lxw_workbook</a>  *workbook  = <a class="code" href="workbook_8h.html#a8ca9bd8c30c618b81ca6180f78b03323">workbook_new_opt</a>(<span class="stringliteral">&quot;constant_memory.xlsx&quot;</span>, &amp;options);</div>
<div class="line">...</div>
<div class="line"> </div>
<div class="line"><span class="comment">// !! Don&#39;t use &quot;column x row&quot; order in &#39;constant_memory&#39; mode. Only</span></div>
<div class="line"><span class="comment">//    the first column of data will be written.</span></div>
<div class="line">for (col = 0; col &lt; max_col; col++) {</div>
<div class="line">    <span class="keywordflow">for</span> (row = 0; row &lt; max_row; row++) {</div>
<div class="line">        <a class="code" href="worksheet_8h.html#ad9fc47d3beaa2ab4759414e8580c2289">worksheet_write_number</a>(worksheet, row, col, 123.45, NULL);</div>
<div class="line">    }</div>
<div class="line">}</div>
</div><!-- fragment --><h1><a class="anchor" id="ww_mem_inline_strings"></a>
Inline strings</h1>
<p>Another optimization that is used to reduce memory usage in <code>constant_memory</code> mode is that cell strings aren't stored in an Excel structure call "shared
strings" and instead are written "in-line".</p>
<p>This is a documented Excel feature that is supported by most spreadsheet applications. However, it isn't supported by some some spreadsheet viewer applications. Also, the size of the output file can increase by 20%-100% depending on the amount of repeated string data.</p>
<h1><a class="anchor" id="ww_mem_performance"></a>
Performance</h1>
<p>Currently the library is optimized but not highly optimized. Also, the library is currently single threaded.</p>
<p>Next: <a class="el" href="working_with_macros.html">Working with VBA Macros</a> </p>
</div></div><!-- contents -->
</div><!-- PageDoc -->
<div class="ttc" id="aworkbook_8h_html_ad9e7aeebc0fd43562db5bcd527b2ee5e"><div class="ttname"><a href="workbook_8h.html#ad9e7aeebc0fd43562db5bcd527b2ee5e">workbook_close</a></div><div class="ttdeci">lxw_error workbook_close(lxw_workbook *workbook)</div><div class="ttdoc">Close the Workbook object and write the XLSX file.</div></div>
<div class="ttc" id="astructlxw__workbook__options_html"><div class="ttname"><a href="structlxw__workbook__options.html">lxw_workbook_options</a></div><div class="ttdoc">Workbook options.</div><div class="ttdef"><b>Definition:</b> workbook.h:254</div></div>
<div class="ttc" id="acommon_8h_html_a57bffaf0ff3cb4e9f4f7f2b0b6dad349a847f574cc183a1d8a4e3800fe28091f4"><div class="ttname"><a href="common_8h.html#a57bffaf0ff3cb4e9f4f7f2b0b6dad349a847f574cc183a1d8a4e3800fe28091f4">LXW_FALSE</a></div><div class="ttdeci">@ LXW_FALSE</div><div class="ttdef"><b>Definition:</b> common.h:51</div></div>
<div class="ttc" id="aworkbook_8h_html_a8ca9bd8c30c618b81ca6180f78b03323"><div class="ttname"><a href="workbook_8h.html#a8ca9bd8c30c618b81ca6180f78b03323">workbook_new_opt</a></div><div class="ttdeci">lxw_workbook * workbook_new_opt(const char *filename, lxw_workbook_options *options)</div><div class="ttdoc">Create a new workbook object, and set the workbook options.</div></div>
<div class="ttc" id="astructlxw__worksheet_html"><div class="ttname"><a href="structlxw__worksheet.html">lxw_worksheet</a></div><div class="ttdoc">Struct to represent an Excel worksheet.</div><div class="ttdef"><b>Definition:</b> worksheet.h:1603</div></div>
<div class="ttc" id="acommon_8h_html_a57bffaf0ff3cb4e9f4f7f2b0b6dad349a7850bcc34c18efe2e45372f4069be0bf"><div class="ttname"><a href="common_8h.html#a57bffaf0ff3cb4e9f4f7f2b0b6dad349a7850bcc34c18efe2e45372f4069be0bf">LXW_TRUE</a></div><div class="ttdeci">@ LXW_TRUE</div><div class="ttdef"><b>Definition:</b> common.h:53</div></div>
<div class="ttc" id="astructlxw__workbook_html"><div class="ttname"><a href="structlxw__workbook.html">lxw_workbook</a></div><div class="ttdoc">Struct to represent an Excel workbook.</div><div class="ttdef"><b>Definition:</b> workbook.h:273</div></div>
<div class="ttc" id="acommon_8h_html_aaf33b4e2179dcc466359277210774ce3"><div class="ttname"><a href="common_8h.html#aaf33b4e2179dcc466359277210774ce3">lxw_row_t</a></div><div class="ttdeci">uint32_t lxw_row_t</div><div class="ttdef"><b>Definition:</b> common.h:40</div></div>
<div class="ttc" id="aworksheet_8h_html_ad9fc47d3beaa2ab4759414e8580c2289"><div class="ttname"><a href="worksheet_8h.html#ad9fc47d3beaa2ab4759414e8580c2289">worksheet_write_number</a></div><div class="ttdeci">lxw_error worksheet_write_number(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, double number, lxw_format *format)</div><div class="ttdoc">Write a number to a worksheet cell.</div></div>
<div class="ttc" id="aworkbook_8h_html_a81d456b4f65a464e78e4a0030ecc3c2e"><div class="ttname"><a href="workbook_8h.html#a81d456b4f65a464e78e4a0030ecc3c2e">workbook_add_worksheet</a></div><div class="ttdeci">lxw_worksheet * workbook_add_worksheet(lxw_workbook *workbook, const char *sheetname)</div><div class="ttdoc">Add a new worksheet to a workbook.</div></div>
<div class="ttc" id="acommon_8h_html_a4fedd8dede664b070e84e850a3f92fd2"><div class="ttname"><a href="common_8h.html#a4fedd8dede664b070e84e850a3f92fd2">lxw_col_t</a></div><div class="ttdeci">uint16_t lxw_col_t</div><div class="ttdef"><b>Definition:</b> common.h:46</div></div>
<!-- HTML footer for doxygen 1.8.20-->
<!-- start footer part -->
<hr class="footer"/><address class="footer"><small>
Copyright 2014-2020 John McNamara.
Generated by&#160;<a href="http://www.doxygen.org/index.html"><img class="footer" src="doxygen.svg" width="104" height="31" alt="doxygen"/></a> 1.8.20
</small></address>
</body>
</html>
